/**
 * 
 */
package dataPersistence.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;

import common.Result;
import common.ValueResult;

import dataPersistence.TransactionManager;
import dataPersistence.dto.ProductDTO;
import dataPersistence.dto.ProductGroupDTO;

/**
 * @author fantastic4
 *
 */
public class ProductGroup_DB_DAO implements IProductGroupDAO
{


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductGroupDAO#read(dataPersistence.dto.ProductGroupDTO)
	 */
	@Override
	public Result read(ProductGroupDTO dto)
	{
		return null;

	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductGroupDAO#add(dataPersistence.dto.ProductGroupDTO)
	 */
	@Override
	public Result add(ProductGroupDTO dto)
	{
		Result result = new Result();
		StorageUnit_DB_DAO suDAO = new StorageUnit_DB_DAO();

		//add to container table
		result =	suDAO.add(dto);

		if(!result.getStatus())
		{
			System.err.println(result.getMessage());
			return result;
		}

		try 
		{
			String query = "insert into ProductGroup (productContainer_ID,threeMonthSupply,threeMonthSupplyUnit) values (?, ?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getProductContainerID());
			stmt.setFloat(2,dto.getThreeMonthsSize()); // covert from util.Date to sql.Date
			stmt.setString(3, dto.getThreeMonthUnit());

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert member");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;


	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductGroupDAO#update(dataPersistence.dto.ProductGroupDTO)
	 */
	@Override
	public Result update(ProductGroupDTO dto)
	{
		StorageUnit_DB_DAO suDAO = new StorageUnit_DB_DAO();
		Result result = new Result();
		
		result=suDAO.update(dto);
		
		if(!result.getStatus())
		{
			System.err.println(result.getMessage());
			return result;
		}
		
		try 
		{
			String query = "update ProductGroup set threeMonthSupply=?,threeMonthSupplyUnit=? where productContainer_ID=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
		
			stmt.setFloat(1,dto.getThreeMonthsSize());
			stmt.setString(2, dto.getThreeMonthUnit());
			stmt.setString(3,dto.getProductContainerID());
			
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update member");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
		

	}


	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductGroupDAO#delete(dataPersistence.dto.ProductGroupDTO)
	 */
	@Override
	public Result delete(ProductGroupDTO dto)
	{
		StorageUnit_DB_DAO suDAO = new StorageUnit_DB_DAO();
		Result result = new Result();
		result = suDAO.delete(dto);
		if(!result.getStatus())
		{
			System.err.println(result.getMessage());
			return result;
		}
		
		try 
		{
			String query = "delete from ProductGroup where productContainer_ID=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
		
			stmt.setString(1,dto.getProductContainerID());
			
			
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update member");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}


	public ValueResult<Set<ProductGroupDTO>> getAllProductGroups()
	{
		ValueResult<Set<ProductGroupDTO>> result = new ValueResult<Set<ProductGroupDTO>>();
		PreparedStatement stmt = null;
		ResultSet rs=null;
		Set<ProductGroupDTO> allProductGroups = new HashSet<ProductGroupDTO>();
		try 
		{
			String query = "select pc.container_ID, pc.name,pc.parent, pg.threeMonthSupply, pg.threeMonthSupplyUnit" +
					" from ProductGroup AS pg JOIN ProductContainer AS pc ON (pc.container_ID=pg.productContainer_ID) ";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				ProductGroupDTO tmp = new ProductGroupDTO();
				tmp.setName(rs.getString(2));
				tmp.setParentID(rs.getString(3));
				tmp.setProductContainerID(rs.getString(1));
				tmp.setThreeMonthsSize(rs.getFloat(4));
				tmp.setThreeMonthUnit(rs.getString(5));
				ValueResult<ArrayList<String>>children=	getChildren(rs.getString(1));
				if(!children.getStatus())
				{
					result.setStatus(false);
					result.setMessage(children.getMessage());
					return result;
				}
				tmp.setChildrenIds(children.getValue());
				allProductGroups.add(tmp);
			}
			result.setValue(allProductGroups);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}


	private ValueResult<ArrayList<String>> getChildren(String string)
	{
		ValueResult<ArrayList<String>> result = new ValueResult<ArrayList<String>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		ArrayList<String> list = new ArrayList<String>();
		try 
		{
			String query = "select pc.container_ID from ProductGroup AS pg JOIN ProductContainer AS pc ON pc.container_ID=pg.productContainer_ID WHERE pc.parent=? ";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1,string);
			
			rs = stmt.executeQuery();
			while (rs.next())
			{
				list.add(rs.getString(1));
			}
			result.setValue(list);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}




}
